﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using dhDataAcess.linqDAL;
using dhDataAcess.baseBLL;
using dhDataAcess.dataAcess.sysDataSetTableAdapters;
using dhDataAcess.dataAcess;
using dhSystemBLL.basicData;

namespace baseSet
{
    public class Query
    {
        private SqlTransaction tran = null;
        private SqlConnection conn = null;
        public Query(SqlTransaction _transaction, SqlConnection _conn)
        {
            tran = _transaction;
            conn = _conn;
        }

        public  SqlDataReader sqlQuery( string tableName,string queryNum)
        //public  DataTable sqlQuery( string tableName,string queryNum)
        {
            queryBLL querybll=new queryBLL ();
            sysDataSet .queryDataTable queryTabel = querybll.getquerybyqueryNumAll(queryNum );
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            
            string selectStr = tableName;
            int i = 0;
            foreach (sysDataSet .queryRow query in queryTabel )
             //   foreach (queryModel query in queryList)
                {
                    if (i == 0)
                    {
                        selectStr += " and " + query.strField +" "+ query.strBJ + "@" + query.strFieldParm;
                    }
                    else
                    {
                        selectStr += query.strLog + query.strField + " " + query.strBJ + "@" + query.strFieldParm;
                    }
                    i++;
                }
            foreach (sysDataSet.queryRow query in queryTabel)
           // foreach (queryModel query in queryList)
            {
                if (query.strBJ == "like")
                {
                    SqlParameter parm = new SqlParameter("@" + query.strFieldParm, "%" + query.strValue + "%");
                    cmd.Parameters.Add(parm);
                }
                else
                {
                    SqlParameter parm = new SqlParameter("@" + query.strFieldParm, query.strValue);
                    cmd.Parameters.Add(parm);
                }
              
            }

            cmd.CommandText = selectStr;
            cmd.Transaction = tran;
            SqlDataReader rdr = cmd.ExecuteReader();
            //SqlDataAdapter da = new SqlDataAdapter();

                return rdr;
        }

        public DataTable sqlQueryLikeAllToTable(string moduleNum, string queryStr)
        {
            moduleListBLL moduleList = new moduleListBLL();
            itemListBLL itemList = new itemListBLL();
            sysDataSet.moduleListDataTable moduleTabel = moduleList.getmoduleListAll(moduleNum);
            sysDataSet.itemListDataTable itemListTabel = itemList.getitemListBymoduleNum(moduleNum);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            sysDataSet.moduleListRow moduleRow;
            string selectStr = null;
            if (moduleTabel.Rows.Count > 0)
            {
                moduleRow = moduleTabel[0];
                selectStr = moduleRow.mark;
            }
            int i = 0;
            foreach (sysDataSet.itemListRow listRow in itemListTabel)
            {
                if (i == 0)
                {
                    selectStr += " and " + listRow.itemName + " like " + "@" + listRow.itemName;
                }
                else
                {
                    selectStr += " or " + listRow.itemName + " like " + "@" + listRow.itemName;
                }

                SqlParameter parm = new SqlParameter("@" + listRow.itemName, "%" + queryStr + "%");
                cmd.Parameters.Add(parm);
                i++;
            }

            cmd.CommandText = selectStr;
            //cmd.Transaction = tran;
            SqlDataReader rdr = cmd.ExecuteReader();
            //SqlDataAdapter da = new SqlDataAdapter();
            DataTable T = new DataTable();
            T.Load(rdr);
            return T;
        }

        public SqlDataReader sqlQueryLikeAll(string moduleNum,string queryStr)
        {
            moduleListBLL moduleList=new moduleListBLL();
            itemListBLL itemList=new itemListBLL();
            sysDataSet.moduleListDataTable moduleTabel =moduleList.getmoduleListAll(moduleNum) ;
            sysDataSet.itemListDataTable itemListTabel = itemList.getitemListBymoduleNum(moduleNum);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
             sysDataSet.moduleListRow moduleRow;
             string selectStr=null;
             if (moduleTabel.Rows.Count > 0)
             {
                 moduleRow = moduleTabel[0];
                  selectStr = moduleRow.mark;
             }
            int i = 0;
            foreach (sysDataSet.itemListRow listRow in itemListTabel)
            {
                if (i == 0)
                {
                    selectStr += " and " + listRow.itemName+" like " + "@" + listRow.itemName;
                }
                else
                {
                    selectStr += " or " + listRow.itemName + " like " + "@" + listRow.itemName;
                }

                SqlParameter parm = new SqlParameter("@" +listRow.itemName, "%"+queryStr+"%");
                cmd.Parameters.Add(parm);
                i++;
            }

            cmd.CommandText = selectStr;
            //cmd.Transaction = tran;
            SqlDataReader rdr = cmd.ExecuteReader();
            SqlDataAdapter da = new SqlDataAdapter();
            return rdr;
        }
        //返回一个表
        public static DataTable   selectALL(string tablename)
        {
            //SqlCommand cmd = new SqlCommand();
            //cmd.Connection = dhDataAcess.baseBLL.dataConn.connOpen();
            //cmd.CommandType = CommandType.Text;
            string selectStr = "select * from " + tablename;
            SqlDataAdapter dt = new SqlDataAdapter(selectStr ,dataConn .connOpen ());
            DataSet ds = new DataSet();
            dt.Fill(ds,"table");
            return ds.Tables ["table"];
            //SqlDataReader rdr = cmd.ExecuteReader();
           // return rdr;
        }
     
 
    }
}
